/*
 * poplawski.tu.koszalin.pl
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package browser;
import java.sql.*;
import javax.swing.*;

/**
 * Klasa menedzera bazy danych
 * @author Adam Kurkiewicz
 */
public class DBManager {

    
   DBManager(){
       connect();
   }
   DBManager(String adres,String port,String baza,String login,String password){
       url+=adres+"/"+baza+":"+port;
       this.login=login;
       this.password=password;
       if(connect()!=true)
       JOptionPane.showMessageDialog(null,"Polaczenie nie powiodlo sie");
   
   }
   public void setLoginData(String adres,String port,String baza,String login,String password){
       url+=adres+"/"+baza+":"+port;
       this.login=login;
       this.password=password;
       if(connect()==true)JOptionPane.showMessageDialog(null,"Polaczenie zostalo nawiazane poprawnie");
       else JOptionPane.showMessageDialog(null,"Polaczenie nie powiodlo sie");
   }
    private boolean connect() {
        try  // zaladowanie sterownika
        {
            Class.forName("org.postgresql.Driver"); // zaladowanie sterownika
        }
        catch(java.lang.ClassNotFoundException e)
        {
           
            JOptionPane.showMessageDialog(null, "Blad: Nie znaleziono sterownika ODBC.");

        }
        
        try  // wykonanie polaczenia
        {
            connect = DriverManager.getConnection(url,login,password); // polaczenie z baza

            stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
           dmd=connect.getMetaData();
            return true;
        }
  
        catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(null,"Wyjatek SQL - Nie udalo się polączenie z bazą danych.\n"+ex);
            return false;
        }
    }
private void close() {
        try{
           stmt.close(); 
           connect.close();
        }catch(SQLException ex) {
              JOptionPane.showMessageDialog(null,"Wyjatek SQL - Blad przy zamykaniu bazy.");
        }   
    }
/**
 * Wykonuje zapytanie do bazy
 * @param index Słowo kluczowe zapytania
 * @param i do adresowania tablicy
 * @return ""
 */
protected String[] columnNames(String table){
    try{
    ResultSetMetaData RSMD=rs.getMetaData();
          int  NumberOfColumns=RSMD.getColumnCount();
        String[] AttributeNames=new String[NumberOfColumns];
            for(int i=0;i<NumberOfColumns;i++)
                AttributeNames[i]=RSMD.getColumnName(i+1);
   return AttributeNames;
    }catch(Exception e){

    }
        return null;
}        
public java.util.Vector xxx(){
    int i=0;
    java.util.Vector<String> s=new java.util.Vector();;
    try{

    DatabaseMetaData m=connect.getMetaData();
    rs =m.getTypeInfo();
    

    while(rs.next()){
       if(Integer.parseInt(rs.getString(2))<1111)
           s.add(rs.getString(1));
    }return s;
    }catch(Exception e){
     JOptionPane.showMessageDialog(null,e.toString());   
    }
    return null;
}
public void createTable(String s){
    try{
        stmt.execute("create table "+s);
    }catch(SQLException e){
       JOptionPane.showMessageDialog(null,e.toString()); 
    }
}
protected Object[][] QueryTable(String table) {
        try {
            
            rs=stmt.executeQuery("SELECT * FROM "+table);
            ResultSetMetaData RSMD=rs.getMetaData();
          int  NumberOfColumns=RSMD.getColumnCount();
          rs.last();
int NumberOfRows=rs.getRow();
rs.beforeFirst();
           Object[][] MyArray=new Object[NumberOfRows][NumberOfColumns];
            int R=0;
            while(rs.next()) {
                for(int C=1; C<=NumberOfColumns;C++)
                    MyArray[R][C-1]=rs.getObject(C);
                R++;}
            return MyArray;
        }catch(Exception e){
      }
        return null;
    }
protected Object[][] QueryAsk(String sql) {
        try {
            rs=stmt.executeQuery(sql);
            ResultSetMetaData RSMD=rs.getMetaData();
          int  NumberOfColumns=RSMD.getColumnCount();
rs.last();
int NumberOfRows=rs.getRow();
rs.beforeFirst();
           Object[][] MyArray=new Object[NumberOfRows][NumberOfColumns];
            int R=0;
            while(rs.next()) {
                for(int C=1; C<=NumberOfColumns;C++)
                    MyArray[R][C-1]=rs.getObject(C);
                R++;}
            return MyArray;
        
}catch(SQLException e){
if(e.getSQLState().equals("02000")==false)
JOptionPane.showMessageDialog(null,e.toString());
      }
        return null;
    }

public String[] catalogs(){
  try{
      
     rs=dmd.getCatalogs();  
     rs.last();
     String[] s=new String[rs.getRow()];
     rs.beforeFirst();
     int i=0;
     while(rs.next()){
         s[i] = rs.getString(1);
      i++;
     }
     return s;

}catch(SQLException e){
    
}return null;
}
public String[] schemas(){
  try{
     
     rs=dmd.getSchemas();  
     rs.last();
     String[] s=new String[rs.getRow()];
     rs.beforeFirst();
     int i=0;
     while(rs.next()){
         s[i] = rs.getString(1);
      i++;
     }
     return s;

}catch(SQLException e){
    
}return null;
}
public void update(String t,String[] c,String[] s){
    String x="";
    try{
        for(int i=1;i<s.length;i++){
           //for(int j=0;j<s[i].length;j++){
            x="update "+t+" set "+c[i]+" = \'"+s[i]+"\' where "+c[0]+" = \'"+s[0]+"\'" ;
            stmt.executeUpdate(x);
        }   
                    
    }catch(SQLException e){
      JOptionPane.showMessageDialog(null,x);     
     JOptionPane.showMessageDialog(null,e.toString());   
    }
}
public String tableInfo(String catalog,String schema,String table){
  try{
     
     rs=dmd.getTablePrivileges(catalog, schema, table);

     rs.last();
     String[] s=new String[rs.getRow()];
     rs.beforeFirst();
     int i=0;
     while(rs.next()){
         s[i] = rs.getString(5);
      i++;
     }
     return s[0];
   }catch(SQLException e){
    
}return null;
}

public String[] tables(String catalog,String schema){
  try{
     
     rs=dmd.getTables(catalog, schema, null, new String[]{"TABLE","SYSTEM TABLE"});
     rs.last();
     String[] s=new String[rs.getRow()];
     rs.beforeFirst();
     int i=0;
     while(rs.next()){
         s[i] = rs.getString(3);
      i++;
     }
     return s;

}catch(SQLException e){
    
}return null;
}
public String[] views(String catalog,String schema){
  try{
     
     rs=dmd.getTables(catalog, schema, null, new String[]{"VIEW","SYSTEM VIEW"});
     rs.last();
     String[] s=new String[rs.getRow()];
     rs.beforeFirst();
     int i=0;
     while(rs.next()){
         s[i] = rs.getString(3);
      i++;
     }
     return s;

}catch(SQLException e){
    
}return null;
}
public String[] tt(){
  try{
     
     rs=dmd.getTableTypes();
     rs.last();
     String[] s=new String[rs.getRow()];
     rs.beforeFirst();
     int i=0;
     while(rs.next()){
         s[i] = rs.getString(1);
      i++;
     }
     return s;

}catch(SQLException e){
    
}return null;
}
  public java.util.Vector<String> ask(){
java.util.Vector<String> v=new java.util.Vector();
    try {
         //  rs=connect.getMetaData().getTables(url, password, login, tabele)
        rs = stmt.executeQuery("SELECT tablename FROM pg_tables where tableowner=\'"+login+"\' order by tablename");
           while(rs.next())
            v.add(rs.getString(1));
               return      v;            
           
}
    
         catch(java.sql.SQLException e){ JOptionPane.showMessageDialog(null,"blad danych sql"+e.toString());}
return null;
}
//public java.util.Vector<String> askQ(String sql){
//java.util.Vector<String> v=new java.util.Vector();
//    try {
//            rs = stmt.executeQuery(sql);
//            if(rs==null)
//           while(rs.next())
//            v.add(rs.getString(1));
//               return      v;            
//            
//}
//    
//         catch(java.sql.SQLException e){ JOptionPane.showMessageDialog(null,"blad danych sql"+e.toString());}
//return v;
//}

public java.util.Vector<String> askViews(){
java.util.Vector<String> v=new java.util.Vector();
    try {
            rs = stmt.executeQuery("SELECT viewname FROM pg_views where viewowner=\'"+login+"\' order by viewname");
           while(rs.next())
            v.add(rs.getString(1));
               return      v;            
           
}
    
         catch(java.sql.SQLException e){ JOptionPane.showMessageDialog(null,"blad danych sql"+e.toString());}
return null;
}
public java.util.Vector<String> askAllTables(){
java.util.Vector<String> v=new java.util.Vector();
    try {
            rs = stmt.executeQuery("SELECT tablename FROM pg_tables order by tablename");
           
            while(rs.next())
            v.add(rs.getString(1));
               return      v;            
           
}
    
         catch(java.sql.SQLException e){ JOptionPane.showMessageDialog(null,"blad danych sql"+e.toString());}
return null;
}
public java.util.Vector<String> askAllViews(){
java.util.Vector<String> v=new java.util.Vector();
    try {
        
            rs = stmt.executeQuery("SELECT viewname FROM pg_views order by viewname");
           while(rs.next())
            v.add(rs.getString(1));
               return      v;            
           
}
    
         catch(java.sql.SQLException e){ JOptionPane.showMessageDialog(null,"blad danych sql"+e.toString());}
return null;
}
/**
 * Dodaje rekordy do bazy to tabeli zamówień
 * @param index
 * @param person
 */

//public void inserts(String[] index,String[] person){
//
//    try {
//            stmt.execute("INSERT INTO orders(id_client,ilosc_sztuk,status_zamowienia,data_zamowienia,id_offer) values ((select id_client from clients where nazwisko_k=\'"+person[0]+"\' and imie_k=\'"+person[1]+"\'),\'"+index[1]+"\',\'Nowe\',CURRENT_DATE,(select id_offer from offer where nazwa=\'"+index[0]+"\'))");
//            stmt.execute("UPDATE offer SET dostepnosc=dostepnosc-"+index[1]+" WHERE id_offer=(select id_offer from offer where nazwa=\'"+index[0]+"\')");
//
//}
//
//         catch(java.sql.SQLException e){ JOptionPane.showMessageDialog(null,"blad danych sql"+e.toString());}
//
//}

 //*
/**
 * Dodaje rekordy do bazy to tabeli klientów
 * @param klient
 */

//public void insert_client(String[] klient){
//
//    try {
//            stmt.execute("insert into clients(imie_k,nazwisko_k,adres_k,miejscowosc_k,kod_pocztowy_k,telefon_k,email_k) VALUES (\'"+klient[0]+"\',\'"+klient[1]+"\',\'"+klient[2]+"\',\'"+klient[3]+"\',\'"+klient[4]+"\',\'"+klient[5]+"\',\'"+klient[6]+"\')");
//
//}
//            catch(java.sql.SQLException e){ JOptionPane.showMessageDialog(null,"blad danych sql"+e.toString());}
//}

/**
     * kończy działanie klasy
     */   
@Override
public void finalize(){
    close();
}
public String[] tabele;
private String url = "jdbc:postgresql://";
private String login="";
        private String password="";
    protected Connection connect=null;
    public Statement stmt;
    public ResultSet rs;
    protected DatabaseMetaData dmd;
}

